#DWS根据需要的维度，分别从DWM获取数据后进行二次聚合，因为DWM已经打包统计过一次，数据较少，所以DWS的统计效率会比较高。

#小时数据和DWM层的数据是一致的，可以直接拿来使用。而年月日数据，则需要group by以后执行sum求和操作。

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;



--小时
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    clue_nums,
    origin_type_stat, 
    for_new_user, 
    hourinfo, 
    dayinfo, 
    monthinfo, 
    '1' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo,' ', dwm.hourinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm;



--天
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    sum(clue_nums) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    dayinfo, 
    monthinfo, 
    '2' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo,'-',dwm.dayinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.dayinfo, dwm.origin_type_stat, dwm.for_new_user;


--月
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    sum(clue_nums) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    '-1' asdayinfo, 
    monthinfo, 
    '4' as time_type, 
    concat(dwm.yearinfo,'-',dwm.monthinfo) as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.monthinfo, dwm.origin_type_stat, dwm.for_new_user;



--年
INSERT INTO itcast_dws.itcast_clue_dws PARTITION(yearinfo)
SELECT 
    sum(clue_nums) as clue_nums,
    origin_type_stat, 
    for_new_user, 
    '-1' as hourinfo, 
    '-1' as dayinfo, 
    '-1' as monthinfo, 
    '5' as time_type, 
    dwm.yearinfo as time_str, 
    yearinfo
from itcast_dwm.itcast_clue_dwm dwm
GROUP BY dwm.yearinfo, dwm.origin_type_stat, dwm.for_new_user;